﻿from django.db import models, connection

PLACE_TYPE = {
    u'1': u'дом', 
    u'2': u'работа', 
    u'3': u'отдых, развлечения', 
    u'4': u'фитнес-клуб', 
    u'5': u'спортзал', 
    u'6': u'секция', 
    u'7': u'спортшкола', 
    u'8': u'школа', 
    u'9': u'ссуз', 
    u'0': u'вуз', 
    u'A': u'военная часть',
}
PLACE_TYPE_CHOICES = tuple(PLACE_TYPE.items())

class L0Object(models.Model):
    
    
    name = models.CharField(u'название', max_length=100, db_index=True)
    abbr = models.CharField(u'сокращение', max_length=10)
    
    def __unicode__(self):
        return unicode(self.abbr)+u'. '+unicode(self.name)
        
    class Meta:
        verbose_name = u'страна'
        verbose_name_plural = u'страны'
    
class L1Object(models.Model):
    

    name = models.CharField(u'наименование', max_length=255, db_index=True)
    abbr = models.CharField(u'сокращение', max_length=50)
    code = models.IntegerField(u'код')
    parent = models.ForeignKey(u'L0Object', verbose_name=u'страна')
    
    def __unicode__(self):
        return unicode(self.abbr)+u'. '+unicode(self.name)
        
    class Meta:
        verbose_name = u'область'
        verbose_name_plural = u'области'
        
    
class L2Object(models.Model):
    

    name = models.CharField(u'наименование', max_length=255, db_index=True)
    abbr = models.CharField(u'сокращение', max_length=50)
    parent = models.ForeignKey(u'L1Object', to_field="code", verbose_name=u'код родителя первого уровня')
    code = models.IntegerField(u'код')
    
    def __unicode__(self):
        return unicode(self.abbr)+u'. '+unicode(self.name)
    
    class Meta:
        verbose_name = u'район'
        verbose_name_plural = u'районы'
        
    
class L34Object(models.Model):
    

    name = models.CharField(u'наименование', max_length=255, db_index=True)
    abbr = models.CharField(u'сокращение', max_length=50)
    l1_parent = models.ForeignKey(u'L1Object', to_field="code", verbose_name=u'код области')
    l2_parent = models.ForeignKey(u'L2Object', to_field="code", verbose_name=u'код родителя')
    
    def get_objects_by_name(name, parent_name=None):
        # TODO: привязка с parent_name!!11
        query = """
        SELECT 
        	geography_l34object.id AS id, 
        	geography_l34object.abbr AS abbr, 
        	geography_l34object.name AS name, 
        	geography_l1object.abbr AS l1_abbr, 
        	geography_l1object.name AS l1_name, 
        	geography_l2object.abbr AS l2_abbr, 
        	geography_l2object.name AS l2_name, 
        	geography_l1object.id AS l1_id, 
        	geography_l2object.id AS l2_id 
        FROM geography_l34object 
	        INNER JOIN geography_l1object 
	        	ON geography_l34object.l1_parent_id = geography_l1object.code 
	        LEFT OUTER JOIN geography_l2object 
	        	ON (geography_l34object.l2_parent_id = geography_l2object.code) OR (geography_l34object.l2_parent_id = 000) 
        WHERE geography_l34object.name LIKE \""""+name+"""%%\" LIMIT 0, 20;"""
        cursor = connection.cursor()
        cursor.execute(query)
        return cursor.fetchall()
    get_objects_by_name = staticmethod(get_objects_by_name)

    #def __unicode__(self):
    #    return unicode(self.abbr)+u'. '+unicode(self.name)
        
    class Meta:
        verbose_name = u'населенный пункт'
        verbose_name_plural = u'населенные пункты'

class Place(models.Model):
    
    
    name = models.CharField(u'название', max_length=255, null=False, db_index=True, blank=True)
    L0Object = models.ForeignKey(L0Object, verbose_name=u'страна', null=True, blank=True)
    L1Object = models.ForeignKey(L1Object, verbose_name=u'область', null=True, blank=True)
    L2Object = models.ForeignKey(L2Object, verbose_name=u'район', null=True, blank=True)
    L34Object = models.ForeignKey(L34Object, verbose_name=u'город', null=True, blank=True)
    street = models.CharField(u'улица', max_length=255, null=True, blank=True)
    house_num = models.CharField(u'номер дома', max_length=3, null=True, blank=True)
    apartament_num = models.CharField(u'номер квартиры/офиса', null=True, blank=True, max_length=10)
    place_type = models.CharField(u'тип места', choices=PLACE_TYPE_CHOICES, max_length=1, blank=True)
    '''
    def get_objects_by_name(name, parent_name=None):
        # TODO: привязка с parent_name!!11
        query = """
        SELECT 
        	geography_l34object.id AS id, 
        	geography_l34object.abbr AS abbr, 
        	geography_l34object.name AS name, 
        	geography_l1object.abbr AS l1_abbr, 
        	geography_l1object.name AS l1_name, 
        	geography_l2object.abbr AS l2_abbr, 
        	geography_l2object.name AS l2_name, 
        	geography_l1object.id AS l1_id, 
        	geography_l2object.id AS l2_id 
        FROM geography_l34object 
	        INNER JOIN geography_l1object 
	        	ON geography_l34object.l1_parent_id = geography_l1object.code 
	        LEFT OUTER JOIN geography_l2object 
	        	ON (geography_l34object.l2_parent_id = geography_l2object.code) OR (geography_l34object.l2_parent_id = 000) 
        WHERE geography_l34object.name LIKE \""""+name+"""%%\" LIMIT 0, 20;"""
        cursor = connection.cursor()
        cursor.execute(query)
        return cursor.fetchall()
    get_objects_by_name = staticmethod(get_objects_by_name)
    '''
    def __unicode__(self):
        return unicode(self.name)
        
    class Meta:
        verbose_name = u'место'
        verbose_name_plural = u'места'

    